# 1、通过python连接pymysql数据库，封装类方法
# 1.1、随机插入100条数据到数据库表，包括姓名、年龄、家庭住址、身份证号码
# 1.2、查询全部的数据
# 1.3、删除指定一条数据
# 1.4、封装批量删除方法，可以删除多条
# 1.5、可以指定增加单独一条数据到表里
# 以上每个操作步骤要写日志信息到log.txt里
import pymysql
from faker import Faker
from random import randint,choice
from datetime import datetime
class DB:
    Faker1 = Faker(locale='zh_cn')
    def connect(self):
        self.db = pymysql.connect(host='121.41.51.49', user="root", password='Aa010903!', database='jiac',
                                  cursorclass=pymysql.cursors.DictCursor)
        self.cursor = self.db.cursor()  # 创建游标

    def close(self):
        self.cursor.close()
        self.db.close()

class Person(DB):
    # create table person(id int primary key auto_increment,name varchar(30) not null,age int,address varchar(50),ssn varchar(50));
    def insert_100_row(self):
        self.connect()
        for i in range(100):
            name = self.Faker1.name() # 姓名
            address = self.Faker1.address() # 地址
            ssn = self.Faker1.ssn(min_age=19,max_age=99) # 身份证号码
            age = randint(19,99)
            str_obj = f"insert into person(name,age,address,ssn) values('{name}','{age}','{address}','{ssn}')"
            try:
                self.cursor.execute(str_obj)
                self.db.commit()
                print("插入成功！")
                with open('log.txt','a',encoding='utf-8') as f:
                    f.write(f"当前时间为：{datetime.now()} ，'{name}','{age}','{address}','{ssn}'" + "插入成功！\n")
            except:
                print(f"insert into person(name,age,address,ssn) values('{name}','{age}','{address}','{ssn}')" +"插入失败！")
                self.db.rollback() # 回滚
                with open('log.txt','a',encoding='utf-8') as f:
                    f.write(f"当前时间为：{datetime.now()} ，'{name}','{age}','{address}','{ssn}'" + "插入失败！\n")
        self.close()
    def seach_all(self):
        self.connect()
        self.cursor.execute("select * from person")
        all_data = self.cursor.fetchall() # [{“id”:1,"name":"xxx"，“age”:19,"address":"xxx","ssn":"202221231"}]
        for i in all_data:
            print(f"当前数据为第：'{i['id']}'条数据,当前姓名为：'{i['name']}',当前年龄为：'{i['age']}'，当前地址为：'{i['address']}'，身份证号码为：'{i['ssn']}")
        self.close()
    def delete_row(self,name):
        self.connect()
        str_obj = f"delete from person where name='{name}'"
        try:
            self.cursor.execute(str_obj)
            self.db.commit()
            print("删除成功！")
            with open('log.txt', 'a', encoding='utf-8') as f:
                f.write(f"当前时间为：{datetime.now()} ，'{name}'" + "删除成功！\n")
        except:
            print("删除失败！")
            self.db.rollback()
            with open('log.txt', 'a', encoding='utf-8') as f:
                f.write(f"当前时间为：{datetime.now()} ，'{name}'" + "删除失败！\n")
        self.close()
    def delete_row_(self,*names):
        self.connect()
        for name in names: #("王丽丽","赵三","王五")
            # 第一次循环：name就是王丽丽
            # 第二次循环：name就是赵三
            str_obj = f"delete from person where name='{name}'"
            # delete from person where name='王丽丽';
            # delete from person where name='赵三';
            try:
                self.cursor.execute(str_obj)
                self.db.commit()
                print("删除成功！")
                with open('log.txt', 'a', encoding='utf-8') as f:
                    f.write(f"当前时间为：{datetime.now()} ，'{name}'" + "删除成功！\n")
            except:
                print("删除失败！")
                self.db.rollback()
                with open('log.txt', 'a', encoding='utf-8') as f:
                    f.write(f"当前时间为：{datetime.now()} ，'{name}'" + "删除失败！\n")
        self.close()
    def insert_row(self,name,age,address,ssn):
        self.connect()
        str_obj = f"insert into person(name,age,address,ssn) values('{name}','{age}','{address}','{ssn}')"
        try:
            self.cursor.execute(str_obj)
            self.db.commit()
            print("插入成功！")
            with open('log.txt', 'a', encoding='utf-8') as f:
                f.write(f"当前时间为：{datetime.now()} ，'{name}','{age}','{address}','{ssn}'" + "插入成功！\n")
        except:
            print(f"insert into person(name,age,address,ssn) values('{name}','{age}','{address}','{ssn}')" + "插入失败！")
            self.db.rollback()  # 回滚
            with open('log.txt', 'a', encoding='utf-8') as f:
                f.write(f"当前时间为：{datetime.now()} ，'{name}','{age}','{address}','{ssn}'" + "插入失败！\n")
        self.close()

# if __name__ == '__main__':
    # 实例化对象
    # p1 = Person()
    # 新增数据
    # p1.insert_100_row()
    # 查询所有
    # p1.seach_all()
    # 删除指定一条数据
    # p1.delete_row('王丽丽')
    # 删除多个数据
    # p1.delete_row_("梁欢","易秀荣","吴桂珍","吴金凤")



# 2、做一个学生点名系统，系统里支持：存入学生、更改学生、删除学生、点到学生姓名方法
#
# 存入学生（姓名、年龄、家庭地址、手机号）： 存入成功后，提示xxx存入成功
# 更改学生（姓名、年龄、家庭地址、手机号）：可以修改学生内容的任意一个字段
# 删除学生：删除的学生将不在展示
# 展示全部学生：可以看到全部的学生姓名
# 学生姓名：随机抽取任意一个存在的学生信息，然后打印：到！。举例： 刘闯到！
#
# 以上内容，需要连接数据库，每次的数据都需要增、删、改、查去做
#
# 3、类作业，统计镰刀和斧头的数量，要使用类方法
#
# 4、新建学生成绩管理系统，随机生成100个 学生信息插入到数据库：
# 学生信息组成：学号、学生姓名、英语成绩、数学成绩、语文成绩、班级号（班级号有：1809A，1809B，1810A，1810B）
#
# 成绩划分为：优秀，良好，中等，及格，不及格。良好比中等好。
#
# 优秀：90—100之间（包括90）
# 良好：80-90
# 中等：70-80
# 及格：60-70
# 不合格：分数低于60分。
#
# 成绩管理系统提供以下方法：
# 《1》 查询所有英语成绩为优秀的、良好的、中等、及格和不合格的学生姓名和班级号
# 《2》 查询1809A，1809B，1810A，1810B每个班级的数学成绩，并且是降序返回
# 《3》 查询1809A，1809B，1810A，1810B每个班级的总人数，并且由大到小排序返回
# 《4》 查询英语、数学、语文成绩不合格的学生姓名和对应的班级号，按照不合格的人数，由大到小排序
#  select name,班级号 from 表 where (english<60 and math<60 and chinese<60) group by 班级号 order by count(*) desc;
class Student_system(DB):
    # create table student1(id int primary key auto_increment,name varchar(30) not null,english int,math int,chinese int,class_id varchar(30));
    def insert_100(self):
        self.connect()
        for i in range(100):
            name = self.Faker1.name()  # 姓名
            english = randint(0,120)
            math = randint(0,120)
            chinese = randint(0,120)
            class_id = choice(["1809A","1809B","1810A","1810B"])
            str_obj = f"insert into student1(name,english,math,chinese,class_id) values('{name}','{english}','{math}','{chinese}','{class_id}')"
            try:
                self.cursor.execute(str_obj)
                self.db.commit()
                print("插入成功！")
                with open('log.txt', 'a', encoding='utf-8') as f:
                    f.write(f"当前时间为：{datetime.now()} ，'{name}','{english}','{math}','{chinese}','{class_id}'" + "插入成功！\n")
            except:
                print(f"insert into person(name,age,address,ssn) values('{name}','{english}','{math}','{chinese}','{class_id}'" + "插入失败！")
                self.db.rollback()  # 回滚
                with open('log.txt', 'a', encoding='utf-8') as f:
                    f.write(f"当前时间为：{datetime.now()} ，'{name}','{name}','{english}','{math}','{chinese}','{class_id}'" + "插入失败！\n")
        self.close()
    # 查询所有英语成绩为优秀的、良好的、中等、及格和不合格的学生姓名和班级号
    def student_info(self):
        # 成绩划分为：优秀，良好，中等，及格，不及格。良好比中等好。
        # 优秀：90—100之间（包括90）
        # 良好：80-90
        # 中等：70-80
        # 及格：60-70
        # 不合格：分数低于60分。
        # english,math,chinese
        self.connect()
        self.cursor.execute("select name,class_id from student1 where english between 90 and 100")
        y_data = self.cursor.fetchall()
        print(f"优秀的学生信息：{y_data}")

        self.cursor.execute("select name,class_id from student1 where english between 80 and 90")
        l_data = self.cursor.fetchall()
        print(f"良好的学生信息：{l_data}")

        self.cursor.execute("select name,class_id from student1 where english between 70 and 80")
        m_data = self.cursor.fetchall()
        print(f"中等的学生信息：{m_data}")

        self.cursor.execute("select name,class_id from student1 where english between 60 and 70")
        j_data = self.cursor.fetchall()
        print(f"及格的学生信息：{j_data}")

        self.cursor.execute("select name,class_id from student1 where english <60")
        n_data = self.cursor.fetchall()
        print(f"不合格的学生信息：{n_data}")
        self.close()
    # 查询1809A，1809B，1810A，1810B每个班级的数学成绩，并且是降序返回
    def select_math_(self):
        self.connect()
        self.cursor.execute("select name,class_id from student1 where class_id='1809A' order by math desc")
        y_data = self.cursor.fetchall()
        print(f"1809A的数学成绩降序返回：{y_data}")

        self.cursor.execute("select name,class_id from student1 where class_id='1809B' order by math desc")
        l_data = self.cursor.fetchall()
        print(f"1809B的数学成绩降序返回：{l_data}")

        self.cursor.execute("select name,class_id from student1 where class_id='1810A' order by math desc")
        m_data = self.cursor.fetchall()
        print(f"1810A的数学成绩降序返回：{m_data}")

        self.cursor.execute("select name,class_id from student1 where class_id='1810B' order by math desc")
        j_data = self.cursor.fetchall()
        print(f"1810A的数学成绩降序返回：{j_data}")
        self.close()
    # 查询1809A，1809B，1810A，1810B每个班级的总人数，并且由大到小排序返回
    def class_count(self):
        self.connect()
        self.cursor.execute("select count(*),class_id from student1 group by class_id order by count(class_id) desc")
        d_data = self.cursor.fetchall()
        print(f"班级人数降序返回：{d_data}")
        self.close()
    # 《4》 查询英语、数学、语文成绩不合格的学生姓名和对应的班级号，按照不合格的人数，由大到小排序
    def student_name_class_id(self):
        self.connect()
        self.cursor.execute("select name,class_id from student1 where (english<60 and math<60 and chinese<60)")
        y_data = self.cursor.fetchall()
        print(f"不合格的学生姓名以及对应班级号：{y_data}")

        # 按照不合格的人数，由大到小排序
        """self.cursor.execute(
            "select count(*) as '人数',class_id from student1 where (english<60 and math<60 and chinese<60) group by class_id order by count(class_id)")
        y_data = self.cursor.fetchall()
        print(f"不合格的人数以及对应班级号：{y_data}")"""

        a_count = 190 # 1809A
        b_count = 99 # 1809B
        c_count = 101 # 1810A
        d_count = 7 # 1810B
        for i in y_data: # python计算每一个班级的人数
            if i['class_id'] == '1809A':
                a_count +=1
            elif i['class_id'] == '1809B':
                b_count +=1
            elif i['class_id'] == '1810A':
                c_count +=1
            elif i['class_id'] == '1810B':
                d_count +=1
        dict_class = {"1809A":a_count,"1809B":b_count,"1810A":c_count,"1810B":d_count}
        items = list(dict_class.items()) # 转成列表   [("1809A",5),("1809B",5),("1810A",5),("1810B",5)]
        n = len(items) # 获取列表长度
        for i in range(n): # 根据这个进行遍历
            for j in range(0, n - i - 1): # 从0开始，每一次循环-1-i
                if items[j][1] > items[j + 1][1]:
                    # 如果说某一个元组的1索引值  大于了  当前索引+1的元组1索引值
                    items[j], items[j + 1] = items[j + 1], items[j] # 那么就把顺序换到前面来
        sorted_dict = dict(items) # 把列表转换成字典
        print("原始字典:", dict_class)
        print("排序后的字典:", sorted_dict)

if __name__ == '__main__':
    # 实例化对象
    s1 = Student_system()

    # 插入100条数据
    # s1.insert_100()

    # 所有英语成绩为优秀的、良好的、中等、及格和不合格的学生姓名和班级号
    # s1.student_info()

    # 数学成绩
    # s1.select_math_()

    # 查询1809A，1809B，1810A，1810B每个班级的总人数，并且由大到小排序返回
    # s1.class_count()

    # 4》 查询英语、数学、语文成绩不合格的学生姓名和对应的班级号，按照不合格的人数，由大到小排序
    s1.student_name_class_id()






# 5、定义一个图书管理系统类，图书可以新增、修改、删除，要求新增、修改、删除的图书，都要出现在数据库中
# 新增：图书名、作者名称、新建年月日
# 修改：图书名、作者名称、新建年月日
# 删除：图书删掉，且不在展示
# 查看：全量查询、精确查询、模糊查询需要用户传入形参来控制返回结果